/*
 * @(#) BasDaoImpl.java  1.0  2004-3-15
 *
 * Copyright 2003 by LongTop Corporation.
 * 2669 XieTu Road, ShangHai, PRC 200030
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * LongTop Corporation ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with LongTop.
 */

package com.cnc.bas.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;





/**
 * @author hongdj
 * @version 1.0  2008-6-4
 */
public class BasDaoImpl extends DAServiceImpl implements IBasDao {
    
	
    public List listSeqSetting() throws DataAccessException {
        return this.getHibernateTemplate().find("from PwSqSetting");
    }
    /**
     * 根据HQL语句查询，返回所有符合条件的list(可用list.size()得到返回的总记录数)。<br>
     * HQL可为完整语句。如select ... from ... where ... order by ... group by ...。<br>
     *
     */
    public List queryAll(final String hql) {
        Object[] result = this.query(hql, null, null, null, -1, -1, false, null, null, null);
        return result[0] != null ? (List) result[0] : null;
    }
    
    public Object[] query(final String hql, final String[] propertyNames, final String[] operators,
            final Object[] values, final int offset, final int size, final boolean isTotalSize,
            final String orderBy, final String groupBy, final String otherCause) {
    	return this.query(hql, propertyNames, operators, values, offset, size, isTotalSize, orderBy, groupBy, otherCause, false);
    }
    /**
     * 根据条件使用HQL语句查询数据。<br>
     * <p/>
     * 具有功能：<br>
     * 1）支持查询分页，该方法会利用数据库本身的分页技术实现。说明如下：<br>
     * &nbsp;&nbsp;&nbsp;&nbsp;a)如果数据库(如MySQL,Oracle,SQL Server2005等)支持limit n,m，查询效率最高；<br>
     * &nbsp;&nbsp;&nbsp;&nbsp;b)如果数据库(如informix,Sybase 12.5.3,SQL Server等)支持top n，查询效率次之（查询结果越大，效率越低）； <br>
     * &nbsp;&nbsp;&nbsp;&nbsp;c)如果以上两种均不支持，查询效率最低。<br>
     * 2）支持查询总记录数<br>
     * 3）支持order by，group by,having等 <br>
     * <p/>
     * 不支持功能：<br>
     * 1）不支持select里的嵌套子查询。如不允许这种用法：select a,b,(select c from table1) as d from table2 ...<br>
     * 2）条件与条件之间不支持or，而是用and。<br>
     * <p/>
     * 示例如下：<br>
     * <pre>
     * 1)查询所有用户信息:
     * <p/>
     * xxxDao.select("from TUser",null,null,null,0,0);
     * <p/>
     * 2)查询用户名含有"admin"开头，注册日期2006-12-01之前前10条用户信息(用户名及注册日期):
     * <p/>
     * xxxDao.select("select userName,createDate from TUser",
     *               new String[]{"userName","createDate"},
     *               new String[]{"like",">="},
     *               new Object[]{"admin%","2006-12-01"},0,10);
     * <p/>
     * <b>注意：对于多对象关联查询，必须指定返回的对象类型。</b><br>
     * 示例如下：
     * <pre>
     * <b>1. 插寻结果bean，A的属性a、b来源于已影射的bean M,N</b>
     * class A{
     *      String a,b;
     *      public A(String a,String b){
     *          this.a=a;
     *          thia.b=b;
     *      }
     * }
     * <p/>
     * <b>2.编写查询方法</b>
     * Query q= session.createQuery("select new A(M.a,N.b) from M as M,N as N where M.id=N.id");
     *
     * @param hql           HQL查询语句（不带Where条件）。不允许在select段内使用子查询，如不允许这种用法：
     *                      select a,b,(select c from table1) as d from table2 ...
     *                      1)对于查询全部对象属性，(<b>select *</b>)不可写。如：from TUser；
     *                      2)对于查询部分对象属性，则必须写完整，如：select userName,password from TUser;
     * @param propertyNames 查询条件的属性名列表
     * @param operators     查询条件的操作符列表，如果查询条件中存在不为<b>=</b>的操作符，需要填写该列表，否则为null，
     *                      应与属性名列表一一对应。操作符包括=, >=, <=, <>, !=, like。
     * @param values        查询条件的值列表，该列表应当与属性列表一一对应
     * @param offset        查询结果的起始行，从0开始。如果不需要，则设置为-1。
     * @param size          查询结果的最大行数。如果不需要，则设置为-1
     * @param isTotalSize   是否需要返回本次查询的总记录数，默认true
     * @param orderBy       排序字符串,不含order by字符串，如orderBy="a desc,b asc",最后生成为：order by a desc,b asc
     * @param groupBy       分组字符串,不含group by 字符串，如groupBy="a desc,b asc",最后生成为：group by a desc,b asc
     * @param otherCause    where后面的其它语句，如排序(order by),分组(group by)及聚集(having)。
     * @param isCache		是否将结果集放置到cache
     *                      如"group by name order by name desc"
     * @return Object[]    有两个值，第一个值表示查询结果列表List list = (List)Object[0]
     *         第二个表示查询返回的总记录数，int count = ((Integer)Object[1]).intValue;
     * @throws com.longtop.intelliwebframework.exception.BaseUncheckedException
     *          基础不可控异常类
     *          </pre>
     */
    public Object[] query(final String hql, final String[] propertyNames, final String[] operators,
                          final Object[] values, final int offset, final int size, final boolean isTotalSize,
                          final String orderBy, final String groupBy, final String otherCause, final boolean isCache)
            throws DataAccessException {

        long l1 = System.currentTimeMillis();
        Map map = (Map) this.getHibernateTemplate().execute(
                new HibernateCallback() {
                    public Object doInHibernate(Session session) throws HibernateException, DataAccessException, SQLException {
                        Query query;
                        String countSql;
                        String fullSql;
                        Integer count = new Integer(0);
                        Map map = new HashMap();
                        String where = "";

                        if (hql == null || hql.trim().equals("")) {
                            logger.error("");
                        }

                        if (propertyNames != null && propertyNames.length > 0 && values != null && values.length > 0) {
                            if (propertyNames.length != values.length) {
                                logger.error("");
                                //throw new BaseUncheckedException(ResourceConstants.E_BASE_0001,
                                       // new Object[]{new Integer(propertyNames.length), new Integer(values.length)});
                            }

                            if (operators != null && propertyNames.length != operators.length) {
                                logger.error("");
                                //throw new BaseUncheckedException(ResourceConstants.E_BASE_0002,
                                        //new Object[]{new Integer(propertyNames.length), new Integer(operators.length)});
                            }

                            for (int i = 0; i <= propertyNames.length - 1; i++) {
                                if ("".equals(where)) {
                                    where = " where ";
                                } else {
                                    where += "and ";
                                }
                                if (operators != null && operators[i].equalsIgnoreCase("isnull")) {
                                    where += propertyNames[i] + " is null ";
                                } else if (operators != null && operators[i].equalsIgnoreCase("isnotnull")) {
                                    where += propertyNames[i] + " is not null ";
                                } else if (operators != null && operators[i].equalsIgnoreCase("isempty")) {
                                    where += propertyNames[i] + " = '' ";
                                } else if (operators != null && operators[i].equalsIgnoreCase("isnotempty")) {
                                    where += propertyNames[i] + " <> '' ";
                                } else {
                                    where += propertyNames[i] + (operators == null || operators[i] == null ? "=" : " " + operators[i]) + " ? ";
                                }
                            }

                            fullSql = hql + where;
//                          modi by hongdj
                            fullSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
                            fullSql += orderBy == null || orderBy.trim().equals("") ? "" : " order by " + orderBy;
                            
                            fullSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

                            query = isCache ? session.createQuery(fullSql).setCacheable(true) : session.createQuery(fullSql);

                            for (int i = 0; i <= values.length - 1; i++) {
                                if (operators != null && operators[i].equalsIgnoreCase("isnull")) continue;
                                if (operators != null && operators[i].equalsIgnoreCase("isnotnull")) continue;
                                if (operators != null && operators[i].equalsIgnoreCase("isempty")) continue;
                                if (operators != null && operators[i].equalsIgnoreCase("isnotempty")) continue;
                                query.setParameter(i, values[i]);
                            }

                        } else {

                            fullSql = hql + where;
                            fullSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
                            fullSql += orderBy == null || orderBy.trim().equals("") ? "" : " order by " + orderBy;
                           
                            fullSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

                            query = session.createQuery(fullSql);
                        }

                        //如果需要统计本次查询总记录数
                        if (isTotalSize) {

                            //生成统计总数查询语句（不能累加order by ，否则效率会受影响）
                            countSql = hql + where;
                            
//                          modi by hongdj
                            
                            countSql += groupBy == null || groupBy.trim().equals("") ? "" : " group by " + groupBy;
                            countSql += otherCause == null || otherCause.trim().equals("") ? "" : " " + otherCause;

                            //生成查询总记录的hql语句。在hql中，不允许在select段内使用子查询，如不允许这种用法：
                            // select a,b,(select c from table1) as d from table2 ...
                            countSql = "select count(*) from " + countSql.substring(countSql.toLowerCase().indexOf("from") + 5);
                            Query query2 = session.createQuery(countSql);

                            if (values != null) {
                                for (int i = 0; i <= values.length - 1; i++) {
                                    if (operators != null && operators[i].equalsIgnoreCase("isnull")) continue;
                                    if (operators != null && operators[i].equalsIgnoreCase("isnotnull")) continue;
                                    if (operators != null && operators[i].equalsIgnoreCase("isempty")) continue;
                                    if (operators != null && operators[i].equalsIgnoreCase("isnotempty")) continue;
                                    query2.setParameter(i, values[i]);
                                }
                            }
                            //输出统计总数查询语句到log
                            logger.debug("countSql=" + query2.getQueryString());
                            //modi by hongdj
                            if(query2.list()!=null&&!query2.list().isEmpty()){
                              count = new Integer(String.valueOf(query2.list().get(0)));
                            }
                        }

                        if (offset > 0) {
                            query.setFirstResult(offset);
                        }

                        if (size > 0) {
                            query.setMaxResults(size);
                        }

                        //输出查询语句到log
                       logger.debug("fullSql=" + query.getQueryString());

                        //  result = Config.getPropertyBool("po.propertyvalue.trimable", true) ? BeanUtil.trim(result) : result;

                        map.put("list", query.list());
                        map.put("count", count);
                        return map;
                    }
                }
        );

        long l2 = System.currentTimeMillis();
       logger.debug("Execute query time:" + (l2 - l1) + "ms");
        return new Object[]{map.get("list"), map.get("count")};
    }
    
    
}
